using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using Obj;

namespace DAL
{

    /// <summary>
    /// Summary description for SQLMarca
    /// </summary>
    public class SQLMarca : dataAccess
    {
        public SQLMarca()
        {
            //
            // TODO: Add constructor logic here
            //
        }


        /// <summary>
        /// Apaga registro da Tabela Marca
        /// </summary>
        /// <param name="marcaID"></param>
        /// <returns></returns>
        public bool Delete(int marcaID)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("marca_Delete", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@marcaID", SqlDbType.Int).Value = marcaID;
                con.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Atualiza registro na Tabela Marca
        /// </summary>
        /// <param name="marca"></param>
        /// <returns></returns>
        public bool Update(objMarca marca)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("marca_Update", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@descricao", SqlDbType.NVarChar).Value = marca.Descricao;
                cmd.Parameters.Add("@marcaID", SqlDbType.Int).Value = marca.MarcaID;
                con.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);

            }
        }


        /// <summary>
        /// Insere um novo registro na tabela Marca
        /// </summary>
        /// <param name="marca"></param>
        /// <returns></returns>
        public int Insert(objMarca marca)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("marca_Insert", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@descricao", SqlDbType.NVarChar).Value = marca.Descricao;
                cmd.Parameters.Add("@marcaID", SqlDbType.Int).Direction = ParameterDirection.Output;
                con.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@marcaID"].Value;
            }
        }

        /// <summary>
        /// Retorna todos os registros da tabela Marca ordenado pelo campo descricao.
        /// </summary>
        /// <returns></returns>
        public List<objMarca> SelectAll()
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("marca_selectAll", con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        }
		
        /// <summary>
        /// Retorna todos os registros da tabela Marca filtrando pela descricao recebida no parametro. 
        /// </summary>
        /// <param name="descricao">descricao da Marca</param>
        /// <returns></returns>
		public List<objMarca> SelectAll(string descricao)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("marca_selectByDescricao", con);
                cmd.CommandType = CommandType.StoredProcedure;
				 cmd.Parameters.Add("@descricao", SqlDbType.NVarChar).Value = descricao;
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        }

        /// <summary>
        /// Retorna o registro da tabela Marca do ID recebido no paramentro
        /// </summary>
        /// <param name="marcaID">Codigo da Marca</param>
        /// <returns></returns>
        public objMarca SelectByID(int marcaID)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("marca_SelectByID", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@marcaID", SqlDbType.Int).Value = marcaID;
                con.Open();
                IDataReader reader = ExecuteReader(cmd);
                if (reader.Read())
                    return obj(reader);
                else
                    return null;
            }
        }

        private objMarca obj(IDataReader reader)
        {
            objMarca marca = new objMarca((int)reader["marcaID"],
                                                    reader["descricao"].ToString()
                                                );
            return marca;
        }

        private List<objMarca> objLista(IDataReader reader)
        {
            List<objMarca> marcas = new List<objMarca>();
            while (reader.Read())
                marcas.Add(obj(reader));
            return marcas;

        }

    }
}